-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL,  -- 角色：'student', 'teacher', 'admin'
    status VARCHAR(20) NOT NULL DEFAULT 'pending',  -- 状态：'pending', 'approved', 'rejected'
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP WITH TIME ZONE
);

-- 创建密保问题表
CREATE TABLE IF NOT EXISTS security_questions (
    id SERIAL PRIMARY KEY,
    question VARCHAR(255) NOT NULL
);

-- 创建用户密保答案表
CREATE TABLE IF NOT EXISTS user_security_answers (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    question_id INTEGER REFERENCES security_questions(id),
    answer_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 创建用户详细信息表
CREATE TABLE IF NOT EXISTS user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    real_name VARCHAR(100),
    student_id VARCHAR(50),
    department VARCHAR(100),
    class_name VARCHAR(100),
    contact_number VARCHAR(20),
    id_type VARCHAR(20),
    id_number VARCHAR(50),
    email VARCHAR(100),
    native_place VARCHAR(100),
    enroll_date DATE,
    ethnicity VARCHAR(20),
    blood_type VARCHAR(10),
    political VARCHAR(20),
    gender VARCHAR(10),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 创建审核记录表
CREATE TABLE IF NOT EXISTS approval_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    admin_id INTEGER REFERENCES users(id),
    action VARCHAR(20) NOT NULL, -- 'approve', 'reject'
    comment TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 创建信息修改申请表
CREATE TABLE IF NOT EXISTS info_modification_requests (
    request_id SERIAL PRIMARY KEY,
    student_id VARCHAR(20) NOT NULL,
    name VARCHAR(50),
    id_type VARCHAR(20),
    id_number VARCHAR(50),
    phone VARCHAR(20),
    native_place VARCHAR(100),
    enroll_date DATE,
    ethnicity VARCHAR(20),
    blood_type VARCHAR(10),
    political VARCHAR(20),
    gender VARCHAR(10),
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 创建课程表
CREATE TABLE IF NOT EXISTS courses (
    id SERIAL PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    course_type VARCHAR(50) NOT NULL,
    teacher VARCHAR(100) NOT NULL,
    credit INTEGER NOT NULL,
    student_count INTEGER NOT NULL,
    classroom VARCHAR(50) NOT NULL,
    weekday VARCHAR(20) NOT NULL,
    section VARCHAR(20) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 创建学生选课表
CREATE TABLE IF NOT EXISTS student_courses (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INTEGER NOT NULL REFERENCES users(id),
    course_id INTEGER NOT NULL REFERENCES courses(id),
    enrollment_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    grade DECIMAL(5,2) DEFAULT NULL,
    status VARCHAR(20) DEFAULT 'enrolled' CHECK (status IN ('enrolled', 'dropped', 'completed')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(student_id, course_id)
);

-- 创建检查学生角色的函数
CREATE OR REPLACE FUNCTION check_student_role()
RETURNS TRIGGER AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM users 
        WHERE id = NEW.student_id 
        AND role = 'student'
    ) THEN
        RAISE EXCEPTION '只有学生角色才能选课';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER check_student_role_trigger
    BEFORE INSERT OR UPDATE ON student_courses
    FOR EACH ROW
    EXECUTE FUNCTION check_student_role();

-- 插入默认的密保问题
INSERT INTO security_questions (question) VALUES
    ('您的出生城市是?'),
    ('您的第一所学校名称是?'),
    ('您的第一个宠物名字是?'),
    ('您母亲的姓名是?'),
    ('您父亲的姓名是?'),
    ('您最喜欢的电影是?'),
    ('您的童年好友名字是?'),
    ('您的第一辆车的品牌是?')
ON CONFLICT DO NOTHING;

-- 创建管理员账号
INSERT INTO users (username, email, password_hash, role, status) 
VALUES ('admin', 'admin@school.edu', '123456', 'admin', 'approved')
ON CONFLICT (username) DO NOTHING; 